
alter proc sp_update_customer_default_shippindAddress
@customer_id int, 
@address_id int, 
@address_type varchar(50) --'Shipping' or 'Billing'

AS


BEGIN

	declare @present_address_id int	
	IF(@address_type = 'Shipping')
	BEGIN
		/*
		 * Update the Default Shipping AddressID in Customer only if it is not same
		 */
		
		SELECT @present_address_id = C_Default_Shipping_Address_ID From Customer
			WHERE Customer_ID = @customer_id
		
		if(@present_address_id <> @address_id)
		begin
			DELETE from Customer_Shipping_Address_Table 
				WHERE Customer_ID = @customer_id and
				Address_ID = @address_id

			UPDATE Customer SET C_Default_Shipping_Address_ID = @address_id
				WHERE Customer_ID = @customer_id
			
			-- insert previous default address id not present in customer shipping address table
			if not exists (SELECT * FROM Customer_Shipping_Address_Table 
				WHERE Customer_ID = @customer_id and Address_ID = @present_address_id)
			begin
				INSERT INTO Customer_Shipping_Address_Table 
					VALUES(@customer_id,@present_address_id,CURRENT_TIMESTAMP)
			end
		
		end
	END
	ELSE --Billing
	BEGIN
		/*
		 * Update the Default Billing AddressID in Customer only if it is not same
		 */
		
		SELECT @present_address_id = C_Default_Billing_Address_ID From Customer
			WHERE Customer_ID = @customer_id
		
		if(@present_address_id <> @address_id)
		begin
			DELETE from Customer_Billing_Address_Table 
				WHERE Customer_ID = @customer_id and
				Address_ID = @address_id

			UPDATE Customer SET C_Default_Billing_Address_ID = @address_id
				WHERE Customer_ID = @customer_id
			
			-- insert previous default address id not present in customer shipping address table
			if not exists (SELECT * FROM Customer_Billing_Address_Table 
				WHERE Customer_ID = @customer_id and Address_ID = @present_address_id)
			begin
				INSERT INTO Customer_Billing_Address_Table 
					VALUES(@customer_id,@present_address_id,CURRENT_TIMESTAMP)
			end
		
		end
	END
END
